package th.co.exat.model;

import com.channgam.dbmlib.sql.MySQLJDBC;
import com.channgam.dbmlib.sql.iDataAdapter;
import java.util.ArrayList;
import java.util.List;
import th.co.exat.model.container.GovContainer;
import th.co.exat.uap.config.Config;

public class Gov {

    private static iDataAdapter i;
    private static int numRows;

    public static int getNumRows(GovContainer g) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT count(*) AS c "
                + "FROM uap_mst_gov "
                + "WHERE 1 ";

        if (g.getPlateFull() != null) {
            sql += "AND plate_full LIKE '%" + g.getPlateFull() + "%' ";
        }

        if (g.getProvince() != null) {
            sql += "AND province LIKE '%" + g.getProvince() + "%' ";
        }
        i.query(sql);

        while (i.resultNext()) {
            numRows = i.result().getInt("c");
        }

        i.close();

        return numRows;
    }

    public static List<GovContainer> getDataList(GovContainer c,
            int showRecord, int page) throws Exception {
        List<GovContainer> l = new ArrayList<GovContainer>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        int gotoPage = 0;
        if (page <= 0) {
            page = 1;
        }

        gotoPage = (page - 1) * showRecord;
        String sql = "SELECT * "
                + "FROM uap_mst_gov "
                + "WHERE 1 ";
        if (c.getPlateFull() != null) {
            sql += "AND plate_full LIKE '%" + c.getPlateFull() + "%' ";
        }

        if (c.getProvince() != null) {
            sql += "AND province = '" + c.getProvince() + "' ";
        }
        sql += " ORDER BY plate_full ";
        sql += "LIMIT " + gotoPage + " , " + showRecord;

        i.query(sql);

        while (i.resultNext()) {
            GovContainer g = new GovContainer();
            g.setCarType(i.result().getString("car_type"));
            g.setPlateFull(i.result().getString("plate_full"));
            g.setProvince(i.result().getString("province"));
            g.setBrand(i.result().getString("brand"));
            g.setOwnerPrefix(i.result().getString("owner_prefix"));
            g.setOwnerFname(i.result().getString("owner_fname"));
            g.setOwnerLname(i.result().getString("owner_lname"));
            l.add(g);
        }

        i.close();
        return l;
    }
}
